﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;
using System.Configuration;
namespace IHISCETeim.DAL
{
    public  class SBODAL
    {
        /// <summary>
        /// SBO连接字符串
        /// </summary>
        protected static string SBOConnStr
        {
            get {
                return "SBOConnectionString";
            }
        }
       
        /// <summary>
        /// 根据序列号获取相关的原始BOM
        /// </summary>
        /// <param name="IntrSerial">序列号</param>
        /// <returns></returns>
        public static DataTable GetBOMByIntrSerial(string IntrSerial, string sbodb)
        {
            Database DB = DatabaseFactory.CreateDatabase(sbodb);
            DbCommand dbcomm = DB.GetStoredProcCommand("ihi_GetGYSByIntrSerial", IntrSerial.ToUpper().Trim());
            return DB.ExecuteDataSet(dbcomm).Tables[0];
        }
        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <param name="CardCode"></param>
        /// <returns>
        /// AliasName(缩写),Name(联系人),Address(地此),tel1(电话),Cellolar(移动电话),Fax(传真)
        /// </returns>
        public static DataTable GetSupplierInfo(string CardCode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT ocrd.CardCode,ocrd.CardName,ocrd.AliasName,ocpr.Name,dbo.OCPR.Address,tel1,Cellolar,dbo.OCPR.Fax FROM ocrd,ocpr WHERE ocrd.CardCode=ocpr.CardCode AND ocrd.CardCode='{0}'";
            sql = string.Format(sql, CardCode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            
            return DB.ExecuteDataSet(dbcomm).Tables[0];
        }
        /// <summary>
        /// 获取物料最后一次采购价(实时)
        /// </summary>
        /// <param name="ItemCode"></param>
        /// <returns>返回格式为  RMB</returns>
        public static string GetOITMLastPurPrice(string ItemCode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT LastPurPrc,LastPurCur,LastPurDat FROM oitm  WHERE ItemCode='{0}'";
            sql = string.Format(sql, ItemCode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt= DB.ExecuteDataSet(dbcomm).Tables[0];
            if (dt.Rows.Count <= 0)
                return "0";
            decimal Price = 0.0m;
            string Currency = "";
            Price = Convert.ToDecimal(dt.Rows[0]["LastPurPrc"].ToString());
            Currency = dt.Rows[0]["LastPurCur"].ToString();
            if (Currency.ToLower() != "rmb")
            {
                Price = Price * GetOrttDate(DateTime.Now, Currency);
            }
            return Price.ToString("F2") ;
        }
        /// <summary>
        /// 获取经销商销售价
        /// </summary>
        /// <param name="ItemCode"></param>
        /// <returns></returns>
        public static string GetOITMAgentPrice(string ItemCode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT isnull(price,0) price FROM ITM1  WHERE ItemCode='{0}' AND PriceList=2";
            sql = string.Format(sql, ItemCode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            if (dt.Rows.Count <= 0)
                return "0";
            decimal Price = 0.0m;
           // string Currency = "";
            Price = Convert.ToDecimal(dt.Rows[0]["price"].ToString());
           // Currency = dt.Rows[0]["LastPurCur"].ToString();
            //if (Currency.ToLower() != "rmb")
            //{
            //    Price = Price * GetOrttDate(DateTime.Now, Currency);
            //}
            return Price.ToString("F6");
        }
        /// <summary>
        /// 获取指定日期的汇率,如找不到小汇率则使用1(相同值)
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        private static decimal GetOrttDate(DateTime date, string Currency)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT * FROM Ortt WHERE ratedate='{0}' and Currency='{1}'";
            sql = string.Format(sql, date.ToShortDateString(), Currency.ToUpper().Trim());
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            if (dt.Rows.Count > 0)
                return Convert.ToDecimal(dt.Rows[0]["Rate"].ToString());
            else
                return 1;
        }

        public static string GetCardInfo(string cardcode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT * FROM OCPR WHERE CardCode='{0}'";
            sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            if (dt.Rows.Count <= 0)
                return "";
            return string.Format("联系人:{0},联系电话:{1},手机:{2},传真：{3}",dt.Rows[0]["name"],dt.Rows[0]["tel1"],dt.Rows[0]["cellolar"],dt.Rows[0]["fax"]);
        }
       

        public static DataTable GetOITMList()
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "select  itemcode,REPLACE(itemname,'翻新件','') itemname,oitm.itmsGrpCod,oitb.itmsgrpnam, oitm.onhand from oitm,oitb where oitm.itmsGrpCod=oitb.itmsGrpCod and phantom='N'  and (oitm.itmsGrpCod=102 or oitm.itmsGrpCod=101)  order by oitm.itmsGrpCod ,itemcode";
            // sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            return dt;
        }
        /// <summary>
        /// 获取物料当前的库位及库存
        /// </summary>
        /// <param name="itemcode"></param>
        /// <param name="whscode"></param>
        /// <returns></returns>
        public static DataTable GetOITMSWWOnHand(string itemcode,string whscode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = string.Format("SELECT SWW,dbo.OITW.OnHand FROM dbo.OITM ,dbo.OITW  WHERE oitm.ItemCode=dbo.OITW.ItemCode AND oitw.WhsCode='{1}' AND  oitm.ItemCode='{0}'", itemcode, whscode);
            // sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            return dt;
        }
        /// <summary>
        /// 获取所有的供就商
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSupplierList()
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "select CardCode,cardname,AliasName,address from ocrd where CardType='S'";
            // sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            DataTable dt = DB.ExecuteDataSet(dbcomm).Tables[0];
            return dt;
        }
        /// <summary>
        /// 获取生产订单对比结果
        /// </summary>
        /// <param name="DocEntry1"></param>
        /// <param name="DocEntry2"></param>
        /// <returns></returns>
        public static DataTableCollection IHISCETeim_OWORComparison(int DocEntry1,int DocEntry2)
        { 
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            DbCommand dbCommand = DB.GetStoredProcCommand("IHISCETeim_OWORComparison", DocEntry1, DocEntry2);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 获取厦门整机库存数据
        /// </summary>
        /// <param name="ItemCode"></param>
        /// <returns></returns>
        public static object Get_W01001_OnHand(string ItemCode)
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = string.Format("SELECT SUM(dbo.OITW.OnHand) FROM OITM ,OITW  WHERE OITM.ItemCode=OITW.ItemCode AND dbo.OITM.ItemCode='{0}'", ItemCode);
            // sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            Object dt = DB.ExecuteScalar(dbcomm);
            return dt;
        }

        /// <summary>
        /// 获取进口整机库存列表
        /// </summary>
        /// <param name="DocEntry1"></param>
        /// <param name="DocEntry2"></param>
        /// <returns></returns>
        public static DataTable GetJKMacList()
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            string sql = "SELECT ItemCode,IntrSerial,WhsName FROM dbo.OSRI ,dbo.OWHS WHERE DBO.OSRI.WhsCode=DBO.OWHS.WhsCode AND  (ItemCode='18VX' OR ItemCode='20VXT' OR ItemCode='17VX301' OR ItemCode='18VX001C') AND Status='0'";
            // sql = string.Format(sql, cardcode);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql);
            return DB.ExecuteDataSet(dbcomm).Tables[0];
        }


        #region 原材料货款支付相关
        /// <summary>
        /// 获取需处理的收货采购订单
        /// </summary>
        /// <param name="sqlwhere">查询条件，不带ＷＨＥＲＥ</param>
        /// <param name="topRow">要获取几行</param>
        /// <returns></returns>
        public static DataTable GetOPDNList(string sqlwhere,int topRow)
        {
            StringBuilder sql =new StringBuilder();
            sql.AppendFormat("SELECT TOP {0} DocEntry, DocNum,CardName,CardCode,DocTotal, TaxDate,CONVERT(Nvarchar(100), U_PayDate, 23) U_PayDate, NumAtCard,Comments ", topRow);  
            sql.Append(" FROM dbo.OPDN ");
            sql.Append(" WHERE Series=40 AND CANCELED='N' AND DocStatus='O' ");
            //查询条件
            if (!sqlwhere.IsEmptyOrNull())
            {
                sql.AppendFormat(" and {0} ", sqlwhere);
            }
            //
            sql.Append(" ORDER BY TaxDate DESC,CardName DESC");
            /////----
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
            return DB.ExecuteDataSet(dbcomm).Tables[0];
               
        }

        /// <summary>
        /// 获取收货采购订单－明细
        /// </summary>
        /// <param name="DocEntry">收货采购单的自增长号码，不是单号</param>
        /// <returns></returns>
        public static DataTable GetOPDNDetail(int DocEntry)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT ItemCode,Dscription,Quantity, Price,  Currency,  WhsCode ,ShipDate,BaseRef FROM dbo.pdn1 WHERE DocEntry={0}", DocEntry);

            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
            return DB.ExecuteDataSet(dbcomm).Tables[0];
        }
        /// <summary>
        /// 更新支付时间
        /// </summary>
        /// <param name="DocEntry"></param>
        /// <param name="payDate"></param>
        /// <returns></returns>
        public static bool UpdateOPDNPayDate(int DocEntry,string payDate)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("UPDATE dbo.OPDN SET U_PayDate='{0}' WHERE DocEntry={1}", payDate,DocEntry);
            try
            {
                Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
                DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
                DB.ExecuteNonQuery(dbcomm);
                return true;
            }
            catch {
                return false;
            }
        }
        #endregion
        /// <summary>
        /// 是否存在相应物料 代码,而且是是可用的，不可用不算
        /// </summary>
        /// <param name="itemcode"></param>
        /// <returns>存在返回物料名称</returns>
        public static string GetHaveItemCode(string itemcode)
        { 
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select itemname from oitm  where UPPER(itemcode)='{0}' AND validFor='Y'", itemcode.Trim().ToUpper());
            try
            {
                Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
                DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
                return DB.ExecuteScalar(dbcomm).ToString();                
            }
            catch {
                return "";
            }
        }
        /// <summary>
        /// 获取呆滞品信息，2015-02-06
        /// </summary>
        /// <returns></returns>
        public static DataTableCollection ihi_yyx_dzb()
        {
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            DbCommand dbcomm2 = DB.GetStoredProcCommand("ihi_MRPPre");
            DB.ExecuteDataSet(dbcomm2);

            DbCommand dbcomm = DB.GetStoredProcCommand("ihi_yyx_dzb");
            return DB.ExecuteDataSet(dbcomm).Tables;
        }

        /// <summary>
        /// 2016-03-17 借件归还报警邮件,预计归还前后三天将开始一直发送
        /// </summary>
        /// <param name="itemcode"></param>
        /// <returns></returns>
        public static DataTableCollection GetJieJianGuiHuanAlert()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT * FROM ufd1 WHERE TableID='OWTR' AND FieldID=12 ");
         //  sql.AppendLine("go");
            sql.AppendLine(" SELECT docnum,taxdate,Filler,ToWhsCode,U_JieJianBuMen,U_GuiHuanShiJian ,ItemCode,Dscription,Quantity,JrnlMemo FROM OWTR, wtr1 WHERE owtr.DocEntry=wtr1.DocEntry AND  Filler='W03001' AND ToWhsCode='W03011' AND DATEDIFF(DAY,GETDATE(),U_GuiHuanShiJian)>=0 AND DATEDIFF(DAY,GETDATE(),U_GuiHuanShiJian)<=3");
           // Common.WriteLog(sql);
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
                DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
                return DB.ExecuteDataSet(dbcomm).Tables;
           
        }

        public static DataTable GetQueryParts(string sqlwhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT OITM.ItemCode,ItemName,ROUND((ISNULL(itm1.Price,0)*1.17),2) Price,(SELECT ROUND((ISNULL(Price,0)*1.17),2) FROM dbo.ITM1  AS customer WHERE customer.ItemCode=OITM.ItemCode AND PriceList=4) AS customerPrice,OnHand FROM dbo.OITM ,dbo.ITM1  WHERE oitm.ItemCode=itm1.ItemCode AND dbo.ITM1.PriceList=2 AND oitm.ItmsGrpCod=102 ");
            //  sql.AppendLine("go");
            sql.AppendLine("and "+sqlwhere);
            // Common.WriteLog(sql);
            Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
            DbCommand dbcomm = DB.GetSqlStringCommand(sql.ToString());
            return DB.ExecuteDataSet(dbcomm).Tables[0];
        }
    }
}
